package signBean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AdminDbManager {

	public Connection getConnection(){
		Connection con = null;
			try{
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sign?useUnicode=true&characterEncoding=UTF8","root","");
				
			}catch(Exception e){
				e.printStackTrace();
			}
		return con;
	}
	
	//�õ������û�����Ϣ
	public List<AdminUser> getUsers(){
		List<AdminUser> list = new ArrayList<AdminUser>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from a_user where id>1";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				AdminUser ad = new AdminUser();
				ad.setId(rs.getInt("id"));
				ad.setDepatement(rs.getString("depatement"));
				ad.setGender(rs.getString("gender"));
				ad.setName(rs.getString("name"));
				ad.setPassword(rs.getString("password"));
				ad.setPhone(rs.getString("phone"));
				ad.setUname(rs.getString("uname"));
				list.add(ad);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	//�õ�Ҫ�޸ĵ��û���Ϣ
	public List<AdminUser> getPerson(String id){
		List<AdminUser> list = new ArrayList<AdminUser>();
		try{
			Statement st = getConnection().createStatement();
			ResultSet rs = st.executeQuery("select * from a_user where id="+id);
			while(rs.next()){
				AdminUser ad = new AdminUser();
				ad.setId(rs.getInt("id"));
				ad.setDepatement(rs.getString("depatement"));
				ad.setGender(rs.getString("gender"));
				ad.setName(rs.getString("name"));
				ad.setPassword(rs.getString("password"));
				ad.setPhone(rs.getString("phone"));
				ad.setUname(rs.getString("uname"));
				list.add(ad);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	//�޸���ͨ�û���Ϣ
	public void modifyPerson(AdminUser us){
		try{
			Statement st = getConnection().createStatement();
			String sql = "update a_user set name='"+us.getName()+"', gender='"+us.getGender()+"', depatement='"+
						us.getDepatement()+"', phone='"+us.getPhone()+"', uname='"
						+us.getUname()+"', password='"+us.getPassword()+"' where id="+us.getId();
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	//�����ͨ�û���Ϣ
	public void addPerson(AdminUser user){
		try{
			Statement st = getConnection().createStatement();
			String sql = "insert into a_user (name,gender,depatement,phone,uname,password) values ('"
				+user.getName()+"','"+user.getGender()+"','"+user.getDepatement()+"','"
				+user.getPhone()+"','"+user.getUname()+"','"+user.getPassword()+"')";
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	//ɾ���û�
	public void deletePerson(String id){
		try{
			Statement st = getConnection().createStatement();
			String sql = "delete from a_user where id="+id;
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	//��ѯ�û���Ϣ
	public List<AdminUser> seekUsers(String name,String gender,String depatement,String phone){
		List<AdminUser> list = new ArrayList<AdminUser>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from a_user where name like '%"+name+"%' and  gender like '%"
				+gender+"%' and depatement like '%"+depatement+"%' and  phone like '%"+phone+"%' and id>1";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				AdminUser user = new AdminUser();
				user.setDepatement(rs.getString("depatement"));
				user.setGender(rs.getString("gender"));
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
				user.setPhone(rs.getString("phone"));
				user.setUname(rs.getString("uname"));
				list.add(user);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
		
	}
	//�õ����пγ̡�
	public List<AdminLesson> getLessons(){
		List<AdminLesson> list = new ArrayList<AdminLesson>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from classname ";
			ResultSet rs =  st.executeQuery(sql);
			while(rs.next()){
				AdminLesson ad = new AdminLesson();
				ad.setClassname(rs.getString("classname"));
				ad.setId(rs.getInt("id"));
				list.add(ad);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return list;
	}
	//�õ�����γ̡�
	public List<AdminLesson> getCourse(String id){
		List<AdminLesson> list = new ArrayList<AdminLesson>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from classname where id="+id;
			ResultSet rs =  st.executeQuery(sql);
			while(rs.next()){
				AdminLesson ad = new AdminLesson();
				ad.setClassname(rs.getString("classname"));
				ad.setId(rs.getInt("id"));
				list.add(ad);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	//��ӿγ�
	public void addLesson(String classname){
		try{
			Statement st = getConnection().createStatement();
			String sql ="insert into classname (classname) value ('"+classname+"')";
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	//�޸Ŀγ�
	public void modifyLesson(String id,String classname){
		try{
			Statement st = getConnection().createStatement();
			String sql = "update classname set classname='"+classname+"' where id="+id;
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	//ɾ��γ�
	public void deleteLesson(String id){
		try{
			Statement st = getConnection().createStatement();
			String sql = "delete from classname where id="+id;
			st.execute(sql);
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	//��ѯ�γ�
	public List<AdminLesson> seekLesson(String classname){
		List<AdminLesson> list = new ArrayList<AdminLesson>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from classname where classname like '%"+classname+"%'";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				AdminLesson ad = new AdminLesson();
				ad.setClassname(rs.getString("classname"));
				ad.setId(rs.getInt("id"));
				list.add(ad);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	//�鿴�û��޸ı?��������Ϣ��
	public List<AdminPeople> getPeople(String uid){
		List<AdminPeople> list = new ArrayList<AdminPeople>();
		try{
			Statement st = getConnection().createStatement();
			String sql = "select * from a_userviewed u join a_signup s on u.sid=s.id join a_user a on a.id=u.uid  where u.uid= "+uid;
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				AdminPeople ap = new AdminPeople();
				ap.setCreatetime(rs.getString("createtime"));
				ap.setGender(rs.getString("gender"));
				ap.setName(rs.getString("s.name"));
				ap.setUname(rs.getString("a.name"));
				ap.setId(rs.getInt("sid"));
				list.add(ap);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	//��ѯ������Ա
	public List<AdminPeople> seekPeople(String name,String createtime,String gender,String phone){
		List<AdminPeople> list =new ArrayList<AdminPeople>();
		try{
			Statement st = getConnection().createStatement();
			String sql ="select * from a_signup where name like '%"+name+"%' and createtime like '%"
						+createtime+"%' and gender like '%"+gender+"%' and phone like '%"+phone+"%'";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				AdminPeople ap = new AdminPeople();
				ap.setName(rs.getString("name"));
				ap.setId(rs.getInt("id"));
				ap.setGender(rs.getString("gender"));
				ap.setCreatetime(rs.getString("createtime"));
				ap.setPhone(rs.getString("phone"));
				list.add(ap);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
}
